package Student;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

public class UpdateC extends JPanel implements ActionListener {
    String saveC=null;
    String saveS=null;
    JTextField 课号1,学号1,学号,课号;
    JButton 修改,查找;
    public UpdateC(){
        try {UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());}
        catch(Exception e){System.err.println("不能设置外观: "+e);}
        学号1=new JTextField(12);
        课号1=new JTextField(12);
        课号=new JTextField(12);
        学号=new JTextField(12);
        修改=new JButton("修改");
        查找=new JButton("查找");
        Box box1=Box.createHorizontalBox();//横放 box
        Box box2=Box.createHorizontalBox();
        Box box3=Box.createHorizontalBox();
        Box box4=Box.createHorizontalBox();
        box1.add(new JLabel("课号:",JLabel.CENTER));
        box1.add(课号);
        box2.add(new JLabel("学号:",JLabel.CENTER));
        box2.add(学号);
        box3.add(修改);
        box4.add(new JLabel("课号:",JLabel.CENTER));
        box4.add(课号1);
        box4.add(new JLabel("学号:",JLabel.CENTER));
        box4.add(学号1);
        box4.add(查找);
        Box boxH=Box.createVerticalBox();//竖放 box
        boxH.add(box1);
        boxH.add(box2);
        boxH.add(box3);
        boxH.add(box3);
        boxH.add(Box.createVerticalGlue());
        修改.addActionListener(this);
        查找.addActionListener(this);
        JPanel picPanel=new JPanel();
        JPanel messPanel=new JPanel();
        messPanel.add(box4);
        picPanel.add(boxH);
        setLayout(new BorderLayout());
        JSplitPane splitV=new JSplitPane(JSplitPane.VERTICAL_SPLIT,messPanel,picPanel);//分割
        add(splitV,BorderLayout.CENTER);
        validate();
    }
    public void actionPerformed(ActionEvent e){
        Object obj=e.getSource();
        Statement stmt=null;
        ResultSet rs=null,rs1=null,rsC=null,rsS=null;
        String sql,sql1,sqlS,sqlC;
        if(obj== 查找 ){if( 课号1.getText().equals("")|| 学号1.getText().equals(""))JOptionPane.showMessageDialog(this,"请填写完成查询的信息！ " );
        else{
            sql1="select * from Student_Course where Course_id='"+课号1.getText()+"' and Student_id='"+学号1.getText()+"'";
            try{
                Connection dbConn1=CONN();
                stmt=(Statement)dbConn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                rs1=stmt.executeQuery(sql1);
                if(rs1.next()){课号.setText(rs1.getString("Course_id").trim());
                    学号.setText(rs1.getString("Student_id").trim());
                    saveC=课号1.getText().trim();
                    saveS=学号1.getText().trim();
                }
                else{JOptionPane.showMessageDialog(this,"没有这个课号的学生" );}
                stmt.close();
                rs1.close();
            }catch(SQLException e1){
                System.out.print("SQL Exception occur.Message is:"+e1.getMessage());
            }
        }
        }
        else{
            if(obj==修改){if(saveC==null||saveS==null)JOptionPane.showMessageDialog(this,"还 没查找需要修改的学生/课程" );
            else{
                if(课号.getText().equals("")||学号.getText().equals("")){
                    JOptionPane.showMessageDialog(this,"课程信息填满才能修改！ " );
                }
                else{
                    sqlC="select * from Course where Course_id='"+课号.getText()+"'";
                    sqlS="select * from Student where Student_id='"+学号.getText()+"'";
                    sql1="select * from Student_Course where Course_id='"+ 课号 .getText()+"' and Student_id='"+ 学号.getText()+"'";
                    sql="update Student_Course set Course_id='"+课号.getText()+"',Student_id='"+学号.getText()+"' where Course_id='"+saveC+"' and Student_id='"+saveS+"'";
                    try{
                        Connection dbConn1=CONN();
                        stmt=(Statement)dbConn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                        rsC=stmt.executeQuery(sqlC);
                        if(rsC.next()){ rsS=stmt.executeQuery(sqlS);
                            if(rsS.next()){
                                if(课号.getText().trim().equals(saveC)&& 学号.getText().trim().equals(saveS)){
                                    stmt.executeUpdate(sql);
                                    JOptionPane.showMessageDialog(this,"添加成功");
                                    saveC=null;
                                    saveS=null;
                                }
                                else{rs1=stmt.executeQuery(sql1);
                                    if(rs1.next()){JOptionPane.showMessageDialog(this,"学生与课程号以存在， 无法 修改");}
                                    else{
                                        stmt.executeUpdate(sql);
                                        JOptionPane.showMessageDialog(this,"添加成功");
                                        saveC=null;
                                        saveS=null;
                                    }
                                    rs1.close();
                                }
                            }
                            else{JOptionPane.showMessageDialog(this,"该学生不存在， 无法修改");}
                            rsS.close();
                        }
                        else{JOptionPane.showMessageDialog(this,"该课程不存在， 无法修改");}
                        rsC.close();
                        stmt.close();
                    }
                    catch(SQLException e1){
                        System.out.print("SQL Exception occur.Message is:"+e1.getMessage());
                    }
                }
            }
            }
        }
    }
    // 连接数据库方法
    public static Connection CONN(){
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加 载JDBC 驱动
        String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=KCGL"; //连接服务器和数据库 test
        String userName = "qi"; //默认用户名
        String userPwd = "123456"; //密码
        Connection dbConn=null;
        try {
            Class.forName(driverName);
            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            System.out.println("Connection Successful!"); //如果连接成功 控制台 输出Connection Successful!
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dbConn;
    }
}